package db;

import java.sql.*;
import java.util.ArrayList;

import util.Number;
import model.Country;

public class DBCountry implements IFCountry
{
	private Connection con;

	public DBCountry()
		{
			con = DBConnection.getInstance().getDBcon();
		}

	public ArrayList<Country> searchCountry(String clause, int num, String search)
	{
		ArrayList<Country> countryList = new ArrayList<Country>();

		ResultSet results;

		Country country = new Country();

		String query = "SELECT country_name, country_id, country_tld FROM country WHERE " + ((clause.length() > 0 ) ? ("" + clause) : "") +
				" ORDER BY country_name ASC;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("searchCountry", query);

		try
			{
				PreparedStatement ps = con.prepareStatement(query);

				if(clause.length() > 0)
					{
						ps.setString(1, (num == -1 ? search : "%" + search + "%"));

						if( num > 1)
							{
								for(int i = 1; i <= num ; i++)
									{
										ps.setString(i, (i == 3 ? search : "%" + search + "%"));
									} // end of for
							} // end of if
					} // end of if
				results = ps.executeQuery();
				while(results.next())
					{
						country = buildCountry(results);

						countryList.add(country);

					} // end of while

				ps.close();
			} // end of try
		catch(Exception e)
			{
				if(DBConnection.doDebug())
					{
						DBConnection.printDebug("searchCountry", query);
						System.out.println("error query exception - searchCountry - SELECT: " + e);
						e.printStackTrace();
					} // end of if
			} // end of catch

		return countryList;
	}
	
	


	private Country buildCountry(ResultSet results) throws SQLException
	{
		Country country = new Country();
		try
			{
				country.setCountryID(results.getInt("country_id"));
				country.setName(results.getString("country_name"));
				country.setTld(results.getString("country_tld"));
			} // end of try
		catch ( Exception e)
			{
				System.out.println("could not build the country, error : " + e);
			} // end of catch

		return country;
	}




	@Override
	public ArrayList<Country> getCountriesByAllInfo(String search)  throws SQLException
	{

		boolean number = Number.isNumber(search);
		return searchCountry("(country_name LIKE ? AND country_tld LIKE ?" + (number ? "OR city.city_id = ?" : "") + " ) " , (number ? 3 : 2), search);
	}




	@Override
	public ArrayList<Country> getCountryByName( String name)  throws SQLException
	{
		return searchCountry("country_name LIKE ?",  1 , name);
	}




	@Override
	public ArrayList<Country> getCountryByTld(String tld) throws SQLException
	{
		return searchCountry("country_tld LIKE ?",  1 , tld);
	}



	@Override
	public int updateCountry(Country country) throws SQLException
		{
			int rc = -1;

			String query = "UPDATE country SET country_name = ?, country_tld = ? WHERE country_id = ?;";

			if(DBConnection.doDebug())
				DBConnection.printDebug("updateCountry", query);

			try
				{
					PreparedStatement ps = con.prepareStatement(query);

					ps.setString(1, country.getName());
					ps.setString(2, country.getTld());
					ps.setInt(3, country.getCountryID());

					rc = ps.executeUpdate();

					ps.close();
				} // end of try
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("updateCountry Error", query);
							e.printStackTrace();
						} // end of if
				} // end of catch

			return rc;
		}

	@Override
	public int createCountry(Country country) throws SQLException
		{
			int rc = -1;

			ResultSet results;

			String query_check = "SELECT country_name FROM country WHERE country_name = ?;";

			if(DBConnection.doDebug())
				{
					DBConnection.printDebug("createCountry", query_check);
				} // end of if
			try
				{
					PreparedStatement ps = con.prepareStatement(query_check);
					
					ps.setString(1,  country.getName());

					results = ps.executeQuery();
					
					if(!results.next())
						{
							String query = "INSERT INTO country(country_name, country_tld)" +
									" VALUES( ?, ?);";
							
							if(DBConnection.doDebug())
								DBConnection.printDebug("createCountry", query);

							try
								{
									ps = con.prepareStatement(query);

									ps.setString(1, country.getName());
									ps.setString(2, country.getTld());
									
									rc = ps.executeUpdate();
								} // end of try
							catch(Exception e)
								{
									if(DBConnection.doDebug())
										{
											DBConnection.printDebug("Error in createCountry", query);
											e.printStackTrace();
										} // end of if
								} // end of catch
						} // end of if
					else
						{
							rc = -1;
						} // end of else
					ps.close();
				}
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("createCountry", query_check);
							e.printStackTrace();
						} // end of if
					
				}
			return rc;
		}

	@Override
	public Country getOne(String clause, String search) throws SQLException
		{
			ResultSet results;

			Country country = null;

			String query = "SELECT country_name, country_id, country_tld FROM country WHERE country_" +clause + "= ?";
			
			if(DBConnection.doDebug())
				DBConnection.printDebug("getOne", query);

			try
				{
					PreparedStatement ps = con.prepareStatement(query);
					
					ps.setString(1, search);

					results = ps.executeQuery();
					if(results.next())
						{
							country = buildCountry(results);
						} // end of if

					ps.close();
				} // end of try
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("getOne", query);
							System.out.println("error query exception - getOne - SELECT: " + e);
							e.printStackTrace();
						} // end of if
				} // end of catch

			return country;
		}

	@Override
	public ArrayList<Country> getAllCountries() throws SQLException
		{
			ArrayList<Country> countryList = new ArrayList<Country>();

			ResultSet results;

			Country country = new Country();

			String query = "SELECT country_name, country_id FROM country ORDER BY country_name ASC;";

			if(DBConnection.doDebug())
				DBConnection.printDebug("getAllCountries", query);

			try
				{
					PreparedStatement ps = con.prepareStatement(query);

					results = ps.executeQuery();
					while(results.next())
						{
							country = new Country();
							country.setName(results.getString("country_name"));
							country.setCountryID(results.getInt("country_id"));
							
							countryList.add(country);

						} // end of while

					ps.close();
				} // end of try
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("getAllCountries", query);
							System.out.println("error query exception - getAllCountries - SELECT: " + e);
							e.printStackTrace();
						} // end of if
				} // end of catch

			return countryList;
		}


}